# -*- coding: utf-8 -*-
import xlrd
from xlrd import xldate_as_tuple
import datetime
import xlwt
import sys
import time

if len(sys.argv) != 2:
	print("invalid argument.\nexample: python test.py 1.xlsx")
	exit(0)

groupA = ['王刚','李刚','陈四','陈五','陈六']
groupAMap = {}
groupBMap = {}
workbook = xlrd.open_workbook(sys.argv[1])
sheet_names= workbook.sheet_names()
#totalSheet = len(sheet_names)
for sheet_name in sheet_names:
	sheet = workbook.sheet_by_name(sheet_name)
	for i in range(sheet.nrows):
		row = sheet.row_values(i)
		if len(row) < 3 or i == 0:
			continue
		#print(i,row)
		name = row[0]
		secs = 0
		if row[2] == '打卡异常':
			secs = 0
		else:
			#workTime = xldate_as_tuple(row[6],0)
			#secs = workTime[3]*3600+workTime[4]*60+workTime[5]
			list = str.split(row[2], ':')
			secs = int(list[0])*3600+int(list[1])*60+int(list[2])
		if name in groupA:
			if name in groupAMap:
				groupAMap[name]['time'] += secs
				groupAMap[name]['total'] += 1
			else:
				groupAMap[name] = {'time': secs, 'total': 1}
		else:
			if name in groupBMap:
				groupBMap[name]['time'] += secs
				groupBMap[name]['total'] += 1
			else:
				groupBMap[name] = {'time': secs, 'total': 1}

workbook = xlwt.Workbook(encoding = 'UTF-8')
worksheet = workbook.add_sheet('Sheet1')
# write headers
headers = ['团队','人数','平均工时','团队MAX','团队MIN']
for i in range(len(headers)):
	worksheet.write(0,i,label = headers[i])

#
groupAResult = {}
groupBResult = {}
totalATime = 0
totalBTime = 0

print('=======================A组=======================')
for k in groupAMap:
	#totalATime += groupAMap[k]['time']
	avgTime = groupAMap[k]['time'] / groupAMap[k]['total']
	groupAResult[k] = avgTime
	totalATime += avgTime
	print('%s: %.1f' % (k, avgTime / 3600))

print('=======================B组=======================')
for k in groupBMap:
	#totalBTime += groupBMap[k]['time']
	avgTime = groupBMap[k]['time'] / groupBMap[k]['total']
	groupBResult[k] = avgTime
	totalBTime += avgTime
	print('%s: %.1f' % (k, avgTime / 3600))

#print(groupAResult)
#print(groupBResult)

# groupA
maxPerson = max(groupAResult, key=groupAResult.get)
maxTime = groupAResult[maxPerson]
minPerson = min(groupAResult, key=groupAResult.get)
minTime = groupAResult[minPerson]

# groupA write value to excel
groupAValues = ['A组',len(groupAResult.keys()),("%.1f" % (totalATime/3600/len(groupAResult.keys()))),("%s(%.1f)" % (maxPerson, maxTime/3600)),("%s(%.1f)" % (minPerson, minTime/3600))]
for i in range(len(groupAValues)):
	worksheet.write(1, i, label = groupAValues[i])

# groupB
maxPerson = max(groupBResult, key=groupBResult.get)
maxTime = groupBResult[maxPerson]
minPerson = min(groupBResult, key=groupBResult.get)
minTime = groupBResult[minPerson]

# groupB write value to excel
groupBValues = ['B组',len(groupBResult.keys()),("%.1f" % (totalBTime/3600/len(groupBResult.keys()))),("%s(%.1f)" % (maxPerson, maxTime/3600)),("%s(%.1f)" % (minPerson, minTime/3600))]
for i in range(len(groupBValues)):
	worksheet.write(2, i, label = groupBValues[i])

# save excel
filename = '%s.xlsx'% (time.strftime('%Y%m%d%H%M%S',time.localtime(time.time())))
workbook.save(filename)

print('输出成功: %s' % filename)